import csv
import getpath
import os
import openpyxl
import myglobal
import config_json
import sample
def getdata_raw(fname):
    xl_book = openpyxl.load_workbook(filename=fname)
    table = xl_book.worksheets[0]
    data = []
    for row in table.values:
        r = []
        for one in row:
            if one is not None:
                r.append(one)
            else:
                r.append(None)
        data.append(r)
    return data #None# dataframe1
def zl_data(data):
    r=[]
    for i in range(len(data)):
        row=[]
        for j in range(len(data[0])):# in data[0]:
            one=data[i][j]
            if i==0 and  (one=="SID Value 1" or one=="Result"):
                row.append(one)
            elif data[0][j] =="SID Value 1" or data[0][j]=="Result":
                if i==1:
                    if one=="Fe%":
                        one="Fe"
                row.append(one)
        r.append(row)
    return r
def zl_data_std(data):
    finish=False
    data=data[13:]
    r=[]
    for i in range(len(data)):
        row=[]
        if i in [1,2,3]:
            pass
        else:
            for j in range(len(data[0])):# in data[0]:
                if j==1:
                    pass
                else:
                    one=data[i][j]
                    if i==0:
                        if one=="Fe%":
                            one="Fe"
                    if j==0 and  one=="Grade High":
                        finish=True #can exit because two loop
                        break
                    row.append(one)
            if finish:
                break
            r.append(row)
    return r
def getdata(fname):
    #SID Value 1#Result
    data0=getdata_raw(fname)
    data=zl_data(data0)
    return data[1:]
def getDataObjects(fname):
    data=getdata(fname)
    items=data[0][1:]
    items=[(one,"") for one in items]
    num=0
    r=[]
    for one in data[1:]:
        s=sample.Sample()
        s.sampleid=one[0]
        concs=one[1:]
        s.num=num
        j=0
        for item in items:
            s.items[item]=concs[j]
            j+=1
        r.append(s)
        num+=1
    return r
def getdata_std(fname):
    data0=getdata_raw(fname)
    data=zl_data_std(data0)
    return data
def getdata0():
    fname=os.path.join(getpath.getpath(),"..","data","spec","4460","ceshiyangpin.XLSX")
    data=getDataObjects(fname)
    return data
def getdata_std0():
    fname=os.path.join(getpath.getpath(),"..","data","spec","4460","biaoyangceshi4460.XLSX")
    data=getdata_std(fname)
    return data
if __name__=="__main__":
    # data=getdata_std0()
    data=getdata0()
    print(data)
    print(data[0])
    print(data[1])
    print(data[2])
    print(data[-1])